﻿/*
'Description:机加车间汇总工时中间表结转
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yuxj
'create Date:2022-11-10
'Rewriter:
'Rewrite Date:
*/
using System;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;
using System.Threading;
using System.Configuration;
using UFC.DBUtility;
using UFC.Common;
using System.Collections;
using System.Collections.Generic;

namespace TransferMesReportForm
{
    public class TransferEmployeeWorkTimeBoard
    {
        //入口        
        string strReportConn = ConfigurationManager.AppSettings["MesReportOracleConn"];
        public void RunMain()
        {
            LogHelp.WriteLog("TransferEmployeeWorkTimeBoard服务循环开始---------------------");

            System.Threading.Thread thAddData = new System.Threading.Thread(new System.Threading.ThreadStart(() =>
            {
                try
                {
                    InsertData();
                }
                catch (Exception e)
                {
                    LogHelp.WriteError("TransferEmployeeWorkTimeBoard服务-添加数据", e);
                }
            }));
            thAddData.Start();
            //线程等待
            do
            {
                System.Threading.Thread.Sleep(500);
            } while (thAddData.ThreadState != System.Threading.ThreadState.Stopped);
            LogHelp.WriteLog("TransferEmployeeWorkTimeBoard服务循环结束---------------------");
        }

        private Dictionary<string, string> GetPara()
        {
            DateTime today = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
            DateTime startMonth = today.AddDays(1 - today.Day);
            Dictionary<string, string> para = new Dictionary<string, string>();
            para.Add("ReportBdate", startMonth.ToString("yyyy-MM-dd"));
            para.Add("ReportEdate", today.ToString("yyyy-MM-dd"));
            return para;
        }
        private DataTable GetEmpWorkData(Dictionary<string, string> para)
        {
            string sql = @"
                    SELECT t.employeeid,
                            NVL(round(SUM(t.employeeWorktime) / 60, 2), 0) worktime
                        FROM (SELECT NVL(wts.workerid, e.employeeid) employeeid,
                                    NVL(e2.fullname, e.fullname) fullname,
                                    NVL(e2.employeename, e.employeename) employeename,
                                    (CASE
                                        WHEN wts.containersteptimesheetid IS NOT NULL THEN
                                        NVL(wts.splitduration, 0)
                                        WHEN wts.containersteptimesheetid IS NULL AND
                                            (wt.adjustdate IS NOT NULL OR
                                            wt.verificationdate IS NOT NULL) THEN
                                        NVL(wt.totalduration, 0)
                                        WHEN wts.containersteptimesheetid IS NULL AND
                                            wt.adjustdate IS NULL AND wt.verificationdate IS NULL THEN
                                        ROUND((sd.durationperunit * (wt.qty - wt.scrapqty)) /
                                            NVL(wt.splitcount, 1),
                                            2)
                                    END) employeeWorktime
                                FROM containersteptimesheet wt
                                left join mes_factory_mapping mf on mf.factoryid=wt.factoryid
                                LEFT JOIN employee e ON e.employeeid = wt.workerid
                                INNER JOIN containercurrentinfo cci ON cci.containerid =
                                                                    wt.containerid
                                LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
                                LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid =
                                                                            wt.containersteptimesheetid
                                LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
                                WHERE mf.factorycode!='603' and wt.checkdate >= TO_DATE('{0}', 'yyyy-mm-dd hh24:mi:ss')
                                AND wt.checkdate <= TO_DATE('{1}', 'yyyy-mm-dd hh24:mi:ss')) t
                        GROUP BY t.employeeid ";
            sql = string.Format(sql, para["ReportBdate"], para["ReportEdate"]);
            DataSet ds = OracleHelper.Query(sql);
            return ds.Tables[0];
        }
        private void InsertData()
        {
            string sql = "truncate table r_employeeworktime ";
            OracleHelper.ExecuteSql(sql, strReportConn);
            Dictionary<string, string> para = GetPara();
            DataTable dt = GetEmpWorkData(para);
            //一次查询多次插入
            var insertSqlList = new ArrayList();
            string insertSql = "insert into r_employeeworktime(employeeid,startdate,enddate,worktime) values('{0}',to_date('{1}','yyyy-mm-dd'),to_date('{2}','yyyy-mm-dd'),{3})";
            foreach (DataRow dr in dt.Rows)
            {
                insertSqlList.Add(string.Format(insertSql, dr["employeeid"], para["ReportBdate"], para["ReportEdate"], dr["worktime"].ToString()));
            }

            if (insertSqlList.Count > 0)
            {
                OracleHelper.ExecuteSqlTran(insertSqlList, strReportConn);
                LogHelp.WriteLog("TransferEmployeeWorkTimeBoard本次插入数据完成,记录数" + insertSqlList.Count + "---------------------");
            }

        }
    }
}
